package com.infore.qualityControl.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.qualityControl.po.Recovery;

@Repository
public interface IRecoveryRepository extends JpaRepository<Recovery, String>, JpaSpecificationExecutor<Recovery> {
	
	/** 根据测试时间与监测点ID、监测项目、时间升序查询 **/
	List<Recovery> findByTimeBetweenAndIndexCodeInAndStationCodeInOrderByTimeAsc(Date startDate, Date endDate,
			List<String> indexCodes, List<String> stationCodes);

	/** 根据测试时间与监测点ID、监测项目、时间倒序查询 **/
	List<Recovery> findByTimeBetweenAndIndexCodeInAndStationCodeInOrderByTimeDesc(Date startDate, Date endDate,
			List<String> indexCodes, List<String> stationCodes);

	/** 根据测试时间与监测点ID、监测项目、时间倒序分页查询 **/
	Page<Recovery> findByTimeBetweenAndIndexCodeInAndStationCodeInOrderByTimeDesc(Date startDate, Date endDate,
			List<String> indexCodes, List<String> stationCodes, Pageable pageable);
	
	/** 根据测试时间与监测点ID、监测项目、加标回收率升序查询 **/
	List<Recovery> findByTimeBetweenAndIndexCodeInAndStationCodeInOrderByRecoveryValueAsc(Date startDate, Date endDate,
			List<String> indexCodes, List<String> stationCodes);
	
	/** 根据测试时间与监测点ID、时间倒序查询 **/
	List<Recovery> findByTimeBetweenAndStationCodeInOrderByTimeDesc(Date startDate, Date endDate,
			List<String> stationCodes);
	
	/** 根据站点ID、监测项分组及时间条件获取实传数量与合格数量 **/
	@Query(value = "SELECT AREA_NAME,BASIN_NAME,OPERATION_NAME,STATION_CODE,STATION_NAME,INDEX_CODE,COUNT(1) COUNT,"  
			+ "SUM(CASE WHEN QUALIFICATION = '合格' THEN 1 ELSE 0 END) QUALIFIED "  
			+ "FROM RECOVERY WHERE TIME BETWEEN :startDate AND :endDate AND STATION_CODE IN (:stationCodes) "
			+ "GROUP BY AREA_NAME,BASIN_NAME,OPERATION_NAME,STATION_CODE,STATION_NAME,INDEX_CODE ORDER BY STATION_CODE", nativeQuery = true)
	List<Map<String, Object>> getRecoveryExamineData(@Param("startDate") Date startDate,
			@Param("endDate") Date endDate, @Param("stationCodes") List<String> stationCodes);
	
	/** 根据站点ID、监测项分组及时间与条件获取质控总数、合格数量与不合格数量 **/
	@Query(value = "SELECT INDEX_CODE,COUNT(1) COUNT," + 
			"SUM(CASE WHEN QUALIFICATION = '合格' THEN 1 ELSE 0 END) QUALIFIED," + 
			"SUM(CASE WHEN QUALIFICATION = '不合格' OR QUALIFICATION IS NULL THEN 1 ELSE 0 END) UNQUALIFIED " + 
			"FROM RECOVERY WHERE TIME BETWEEN :startDate AND :endDate " + 
			"AND STATION_CODE IN (:stationCodes) AND INDEX_CODE IN (:indexCodes) GROUP BY INDEX_CODE", nativeQuery = true)
	List<Map<String, Object>> getQualifiedData(@Param("startDate") Date startDate,
			@Param("endDate") Date endDate, @Param("stationCodes") List<String> stationCodes, @Param("indexCodes") List<String> indexCodes);
	
	/** 根据站点ID获取最新的数据时间 **/
	@Query(value = "SELECT TO_CHAR(MAX(TIME),'yyyy-MM-dd HH24:mi:ss') TIME "
			+ "FROM RECOVERY WHERE STATION_CODE = :stationCode ", nativeQuery = true)
	List<Map<String, Object>> getSingleLatestData(@Param("stationCode") String stationCode);
}
